package service;

import dao.TestDao;
import org.apache.commons.math3.stat.StatUtils;
import org.apache.poi.hssf.usermodel.HSSFObjectData;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;


public class ExcelExtractor {
    public static void main(String[] args) {
        File folder = new File("D:\\ouman");   // 指定存放日志的文件夹路径
        File[] files = folder.listFiles();
        for (File file : files) {
            String fileName = file.getName();
            if (!fileFind(fileName)) {
                String excelFilePath = "D:\\ouman\\"+fileName;
                try (FileInputStream fileInputStream = new FileInputStream((excelFilePath));
                     HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream)) {
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        // 获取每一个工作表
                        Sheet sheet = workbook.getSheetAt(i);

                        if (sheet.getSheetName().contains("开料表")) {
                            //创建集合用于存放每一行的数据
                            ArrayList<String[]> list = new ArrayList<>();
                            String s = "";
                            for (Row row : sheet) { // 迭代行
                                String[] str = new String[10];
                                int j = 0;
                                for (Cell cell : row) { // 迭代列
                                    // 获取单元格数据，这里假设我们只处理字符串
                                    String cellValue = getCellValueAsString(cell);
                                    if (!cellValue.isEmpty()) {
                                        str[j++] = cellValue;
                                        //    System.out.print(cellValue + "\t");
                                    }
                                }
                                if (!isRowEmpty(row)){
                                    if (getCellValueAsString(row.getCell(6))!=""){
                                        s = getCellValueAsString(row.getCell(6));
                                    }
                                    str[5] = s;
                                }
                                if(!isRowEmpty(row)&&getCellValueAsString(row.getCell(15))!=""){
                                            str[6] = getCellValueAsString(row.getCell(15));
                                            //System.out.println("================"+row.getCell(15).getStringCellValue());
                                }
                                list.add(str);
                            }

                            extract(list);
                            System.out.println(fileName+"：提取成功");
                        }
                    }
                }
                catch (IOException e) {
                    e.printStackTrace();
                }
            }else {
                continue;
            }
        }

    }

    public static Boolean fileFind(String fileName){
        String filePath = "D:\\oumans\\filename.txt";
        Boolean result = false;
        try {
            BufferedReader reader = new BufferedReader(new FileReader(filePath));
            String line;
            while ((line = reader.readLine())!=null){
                if (line.equals(fileName)) {
                    result = true;
                    //System.out.println(fileName+"：已提取，请勿重复提取！");
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (result==false){
            //不存在该文件，将该文件名记录在filename.xlsx中
            try {
                BufferedWriter writer = new BufferedWriter(new FileWriter(filePath, true));
                writer.newLine();
                writer.write(fileName);
                System.out.println(fileName+"已成功添加到："+filePath);
                writer.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    private static void extract(ArrayList<String[]> list) {
        //写入文件路径
        String filePath = "D:\\oumantest\\";
        ArrayList<TestDao> testDaoList = new ArrayList<>();
        String unitCabinet = "";
        //遍历集合
        for (int i = 0; i < list.size(); i++) {
            int j = 0;
            for (String s : list.get(i)) {
                if (i == 5 && j == 0) {
                    unitCabinet = s;
                    break;
                }
                j++;
            }
        }
        for (int i = 0; i < list.size(); i++) {
            TestDao testDao = new TestDao();
//            Boolean flag = false;
            int j = 0;
            for (String s : list.get(i)) {
                if (s != null) {
                if (s.contains("订单号:")) {
                    String[] split = s.split(":");
                    filePath += split[1] + "-";
                }
                }
                if (i == 5 && j == 1) {
                    filePath += s + "-";
                }
                if (i == 5 && j == 2) {
                    filePath += String.valueOf((int) Double.parseDouble(s)) + "套.xlsx";
                }
                    if (i >= 8 && j <= 6) {
                        switch (j) {
                            case 0:
                                testDao.setName(s);
                                break;
                            case 1:
                                testDao.setLength(s);
                                break;
                            case 2:
                                testDao.setWidth(s);
                                break;
                            case 3:
                                testDao.setHeight(s);
                                break;
                            case 4:
                                if (s!=null){
                                    String[] split = s.split("\\*");
                                    testDao.setNumber(split[1]);
                                }
                                break;
                            case 5:
                                testDao.setColor(s);
                                break;
                            case 6:
                                if (s==null){
                                    s = "";
                                }
                                testDao.setCode(s);
                            default:
                                break;
                        }
                    }
                    testDao.setUnitCabinet(unitCabinet);
                    testDao.setMaterial(10);
                    j++;
//                }
            }
            if (testDao.getName() != null) {
                testDaoList.add(testDao);
            }
        }
        System.out.println("文件名为：" + filePath);

//        for (TestDao dao : testDaoList) {
//            System.out.println("名称：" + dao.getName() + "长：" + dao.getLength() + "宽：" + dao.getWidth() + "高：" + dao.getHeight()
//                    + "数量：" + dao.getNumber() + "唯一编码：" + dao.getCode() + "花色：" + dao.getColor() + "单元柜：" + dao.getUnitCabinet() + "基材：" + dao.getMaterial());
//        }
        fileWrite(testDaoList, filePath);
    }

    private static void  fileWrite(ArrayList<TestDao> list, String filePath) {
        try {
            Boolean flag = true;
            File file = new File(filePath);
            if (file.exists()) {
                flag = false;
            }
            BufferedWriter testWriter = new BufferedWriter(new FileWriter(filePath, true));

            if (flag){
                ArrayList<String> arrayList = new ArrayList<>();
                arrayList.addAll(Arrays.asList("名称", "长", "宽", "高", "数量", "唯一编码", "花色", "单元柜", "基材"));
                for (String s : arrayList) {
                    testWriter.write(s + "\t");
                }
                testWriter.newLine();
            }
            for (int i = 0; i < list.size(); i++) {
                testWriter.write(list.get(i).getName() + "\t");
                testWriter.write(list.get(i).getLength() + "\t");
                testWriter.write(list.get(i).getWidth() + "\t");
                testWriter.write(list.get(i).getHeight() + "\t");
                testWriter.write(list.get(i).getNumber() + "\t");
                testWriter.write(list.get(i).getCode() + "\t");
                testWriter.write(list.get(i).getColor() + "\t");
                testWriter.write(list.get(i).getUnitCabinet() + "\t");
                testWriter.write(list.get(i).getMaterial() + "\t");
                testWriter.newLine();
            }
            testWriter.flush();
            System.out.println(filePath+"文件写入成功！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String getCellValueAsString(Cell cell) {
        String cellValue = "";
        switch (cell.getCellType()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

    public static boolean isRowEmpty(Row row) {
        for (int c = 0; c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }
}